package com.qf.index;

import cn.hutool.core.io.FileUtil;
import cn.hutool.core.util.NumberUtil;
import cn.hutool.db.Db;
import cn.hutool.db.Entity;
import com.alibaba.fastjson.JSONObject;
import lombok.Data;

import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.concurrent.ConcurrentHashMap;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

public class DeptJournal {

    /**
     * 各科室的核心期刊，先从期刊关联表（jourMergeTable）中获取中科院一区或二区或其它的TOP期刊，共计2818本，2019年及以来的论文合计3190128篇，再按中科院的科目使用通义，获取复旦或STEM的科室（方法Dept.tongYiDeptName），最后写入字典中（filePath）。
     * fuDan_呼吸科_51785_Eur Respir J
     * STEM_呼吸病学_51785_Eur Respir J
     * 数据格式如上，共分为4个部分，分别是复旦或STEM的科室、科室、JID、期刊，使用时需要注意，同一个科室下可能有多本期刊。
     * 贾新志 2025.02.15
     */

    public static void main(String[] args) throws SQLException {
        SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        String newDataName = "scholar_25_02.";
        String zkyTopJourMedTable = newDataName + "zky_1_2_jour_med";
        String jourMergeTable = newDataName + "basic_jour_merge";
        String ncTextTable = "html.nc_text_table_0320";
        String fullPathTable = "dir.ncbijournalpdf_20250318_fullpath";
        String downFullPathTable = newDataName + "zky_1_2_jour_med_fullpath";

//        Set<String> nlmIdSet = insertZkyJourTable(jourMergeTable, "id", "nc_nlm_title_abbreviation`,`zky_dalei`,`zky_subcategory`,`zky_category`,`nc_nlmid_list", zkyTopJourMedTable);
//        System.out.println(format.format(new Date()) + " nlmIdSet.size()：" + nlmIdSet.size());
//
//        Map<Integer, String> pmIdAndNlmIdMap = getPmIdAndNlmIdMap(ncTextTable, "id", "ta`,`dp`,`pmid`,`jid", nlmIdSet);
//        System.out.println(format.format(new Date()) + " pmIdAndNlmIdMap.size()：" + pmIdAndNlmIdMap.size());
//
//        insertFullPathTable(fullPathTable, "pid", "fullpath", pmIdAndNlmIdMap, downFullPathTable);
//
//        String user = "Bearer sk-c65ee8baf0c440238e51f74495107da3";
//        for (int i = 0; i < 10; i++) {
//            Dept.tongYiDeptName(zkyTopJourMedTable, "id", "info`,`fudan`,`stem`,`you_dao", user, 0, 100);
//        }
//
//        String filePath = "F:\\WorkSpaces\\Data\\src\\main\\resources\\dict\\dept_top_journal_zky.txt";
//        FileUtil.appendUtf8String("2025.02.15 各科室的top期刊，基于中科院" + System.lineSeparator(), filePath);
//        saveDeptTopJournal(zkyTopJourMedTable, "id", "journal`,`jid`,`fudan`,`stem", filePath);

        updateDeptName(zkyTopJourMedTable, "id", "fudan`,`stem`,`nc_nlmid_list");

    }

    public static void updateDeptName(String zkyTopJourMedTable, String autoId, String field) throws SQLException {
        int onePageNum = 1000;
        List<Entity> tableNum = Db.use().query("select count(1) as count from " + zkyTopJourMedTable);
        Set<String> stemSet = uSet.getStemSet();
        Set<String> fuDanSet = uSet.getFuDanSet();
        if (tableNum.get(0).getInt("count") > 0) {
            int tableMax = Db.use().queryNumber("select max(" + autoId + ") from " + zkyTopJourMedTable).intValue();
            Collection<Integer> tableList = new ArrayList<>();
            NumberUtil.appendRange(0, tableMax + onePageNum, onePageNum, tableList);
            for (Integer startId : tableList) {
                List<Entity> tableData = Db.use().query("select `" + autoId + "`,`" + field + "` from " + zkyTopJourMedTable + " where " + autoId + " between " + startId + " and " + (startId + onePageNum - 1));
                for (Entity entry : tableData) {
                    Integer id = entry.getInt(autoId);
                    if (null != id && id > 0) {
                        List<String> ncNlmidList = Utils.getFieldList(entry, "nc_nlmid_list");
                        try {
                            Db.use().update(
                                    Entity.create().set("fudan_result", getDeptName(entry.getStr("fudan"), fuDanSet)).set("nlmid", ncNlmidList.isEmpty() ? "" : ncNlmidList.getFirst())
                                            .set("stem_result", getDeptName(entry.getStr("stem"), stemSet)),
                                    Entity.create(zkyTopJourMedTable).set(autoId, id));
                        } catch (SQLException e) {
                            e.printStackTrace();
                        }
                    }
                }
            }
        }
    }

    public static String getDeptName(String fuDan, Set<String> fuDanSet) {
        if (null != fuDan && fuDan.length() > 1) {
            if (fuDanSet.contains(fuDan)) {
                return fuDan;
            } else if (fuDan.contains("\"}]")) {
                List<En.UnitInfo> unitInfoList = JSONObject.parseArray(fuDan, En.UnitInfo.class);
                for (En.UnitInfo unitInfo : unitInfoList) {
                    String unit = unitInfo.getUnit();
                    unit = unit.startsWith("[") && unit.endsWith("]") ? unit.substring(1, unit.length() - "]".length()) : unit;
                    if (fuDanSet.contains(unit)) {
                        return unit;
                    }
                }
            }
        }
        return "";
    }

    public static void saveDeptTopJournal(String zkyTopJourMedTable, String autoId, String field, String filePath) throws SQLException {
        int onePageNum = 1000;
        Set<String> stemSet = uSet.getStemSet();
        Set<String> fuDanSet = uSet.getFuDanSet();
        int tableMax = Db.use().queryNumber("select max(" + autoId + ") from " + zkyTopJourMedTable).intValue();
        if (tableMax > 0) {
            Collection<Integer> tableList = new ArrayList<>();
            NumberUtil.appendRange(0, tableMax + onePageNum, onePageNum, tableList);
            for (Integer startId : tableList) {
                List<Entity> tableData = Db.use().query("select `" + autoId + "`,`" + field + "` from " + zkyTopJourMedTable + " where " + autoId + " between " + startId + " and " + (startId + onePageNum - 1));
                for (Entity entry : tableData) {
                    String journal = Utils.getStrField(entry, "journal");
                    int jid = Utils.getIntField(entry, "jid");
                    if (jid > 0 && journal.length() > 2) {
                        saveFile(entry.getStr("fudan"), "fuDan", filePath, jid, journal, fuDanSet);
                        saveFile(entry.getStr("stem"), "STEM", filePath, jid, journal, stemSet);
                    }
                }
            }
        }
    }

    public static void saveFile(String fuDanOrStem, String type, String filePath, int jid, String journal, Set<String> stemOrFuDanSet) {
        if (null != fuDanOrStem && fuDanOrStem.contains("\"}]")) {
            List<En.UnitInfo> unitInfoList = JSONObject.parseArray(fuDanOrStem, En.UnitInfo.class);
            for (En.UnitInfo unitInfo : unitInfoList) {
                String dept = unitInfo.getUnit().trim();
                dept = dept.startsWith("[") && dept.endsWith("]") ? dept.substring(1, dept.length() - 1).trim() : dept;
                System.out.println("dept: " + dept);
                if (stemOrFuDanSet.contains(dept)) {
                    FileUtil.appendUtf8String(type + "_" + dept + "_" + jid + "_" + journal + System.lineSeparator(), filePath);
                }
            }
        }
    }

    public static void putStrEnStrInt(String type, String name, Map<String, Map<String, Integer>> strStrIntMap) {
        if (null != type && type.length() > 1 && null != name && name.length() > 1 && !Utils.isContainChinese(name)) {
            strStrIntMap.compute(type, (k, v) -> {
                if (v == null) {
                    v = new ConcurrentHashMap<>();
                }
                v.compute(name, (key, value) -> value != null ? (value + 1) : 1);
                return v;
            });
        }
    }

    public static Set<String> insertZkyJourTable(String jourMergeTable, String autoId, String field, String zkyTopJourMedTable) throws SQLException {
        int onePageNum = 1000;
        Db.use().execute("truncate table " + zkyTopJourMedTable);
        int tableMax = Db.use().queryNumber("select max(" + autoId + ") from " + jourMergeTable).intValue();
        Set<String> nlmIdSet = new HashSet<>();
        if (tableMax > 0) {
            Collection<Integer> tableList = new ArrayList<>();
            NumberUtil.appendRange(0, tableMax + onePageNum, onePageNum, tableList);
            for (Integer startId : tableList) {
                List<Entity> tableData = Db.use().query("select `" + autoId + "`,`" + field + "` from " + jourMergeTable + " where " + autoId + " between " + startId + " and " + (startId + onePageNum - 1));
                for (Entity entry : tableData) {
                    int zkyDaLei = Utils.getIntField(entry, "zky_dalei");
                    int jid = Utils.getIntField(entry, autoId);
                    String journal = Utils.getStrField(entry, "nc_nlm_title_abbreviation");
                    String top = Utils.getStrField(entry, "zky_category").contains("\"top\":\"是\"") ? "是" : "否";
                    String nlmIds = entry.getStr("nc_nlmid_list");
                    if ((zkyDaLei == 1 || zkyDaLei == 2 || top.equals("是")) && jid > 0 && (journal.length() > 2 || (null != nlmIds && nlmIds.contains("\"]")))) {
                        String zkySubcategory = entry.getStr("zky_subcategory");
                        List<ZkyCategory> zkyList = null != zkySubcategory && zkySubcategory.contains("\"}]") ? JSONObject.parseArray(zkySubcategory, ZkyCategory.class) : new ArrayList<>();
                        for (ZkyCategory category : zkyList) {
                            String name = category.getName();
                            String fq = category.getFq();
                            // 中科院大类一区，或者大类二区或者所有的TOP期刊，并且小类必须是一区或二区。
                            if (fq.equals("1") || fq.equals("2")) {
                                Matcher matcher = Pattern.compile("[\\u4e00-\\u9fa5：]+").matcher(name);
                                while (matcher.find()) {
                                    String group = matcher.group().trim();
                                    if (group.length() > 1 && journal.length() > 2) {
                                        try {
                                            Db.use().insert(Entity.create(zkyTopJourMedTable).set("info", group).set("jid", jid).set("zky_dalei", zkyDaLei).set("top", top).set("you_dao", "you_dao")
                                                    .set("journal", journal).set("zky_xiaolei", Integer.parseInt(fq)).set("nc_nlmid_list", nlmIds));
                                            nlmIdSet.add(journal.toUpperCase().trim());
                                        } catch (SQLException e) {
                                            e.printStackTrace();
                                        }
                                    }
                                }
                                if (null != nlmIds && nlmIds.contains("\"]")) {
                                    List<String> ncNlmidList = JSONObject.parseArray(nlmIds, String.class);
                                    for (String nlmId : ncNlmidList) {
                                        if (null != nlmId && nlmId.length() > 1) {
                                            nlmIdSet.add(nlmId.toUpperCase().trim());
                                        }
                                    }
                                }
                            }
                        }
                    }
                }
            }
        }
        return nlmIdSet;
    }

    public static Map<Integer, String> getPmIdAndNlmIdMap(String ncTextTable, String autoId, String field, Set<String> nlmIdSet) throws SQLException {
        int onePageNum = 100000;
        int tableMax = Db.use().queryNumber("select max(" + autoId + ") from " + ncTextTable).intValue();
        Map<Integer, String> pmIdAndNlmIdMap = new ConcurrentHashMap<>();
        if (tableMax > 0) {
            Collection<Integer> tableList = new ArrayList<>();
            NumberUtil.appendRange(0, tableMax + onePageNum, onePageNum, tableList);
            tableList.parallelStream().forEach(startId -> {
                try {
                    List<Entity> tableData = Db.use().query("select `" + autoId + "`,`" + field + "` from " + ncTextTable + " where " + autoId + " between " + startId + " and " + (startId + onePageNum - 1));
                    for (Entity entry : tableData) {
                        String ta = Utils.getStrField(entry, "ta").toUpperCase().trim();
                        String jid = Utils.getStrField(entry, "jid").toUpperCase().trim();
                        if (nlmIdSet.contains(jid) || nlmIdSet.contains(ta)) {
                            String dp = Utils.getStrField(entry, "dp");
                            if (dp.length() > 3 && NumberUtil.isInteger(dp.substring(0, 4))) {
                                int pmId = Utils.getIntField(entry, "pmid");
                                if (Integer.parseInt(dp.substring(0, 4)) > 2018 && pmId > 0) {
                                    pmIdAndNlmIdMap.put(pmId, jid);
                                }
                            }
                        }
                    }
                } catch (SQLException e) {
//                    throw new RuntimeException(e);
                }
            });
        }
        nlmIdSet.clear();
        return pmIdAndNlmIdMap;
    }

    public static void insertFullPathTable(String fullPathTable, String autoId, String field, Map<Integer, String> pmIdAndNlmIdMap, String downFullPathTable) throws SQLException {
        int onePageNum = 100000;
        Db.use().execute("truncate table " + downFullPathTable);
        int tableMax = Db.use().queryNumber("select max(" + autoId + ") from " + fullPathTable).intValue();
        if (tableMax > 0) {
            Collection<Integer> tableList = new ArrayList<>();
            NumberUtil.appendRange(0, tableMax + onePageNum, onePageNum, tableList);
            for (Integer startId : tableList) {
                List<Entity> tableData = Db.use().query("select `" + autoId + "`,`" + field + "` from " + fullPathTable + " where " + autoId + " between " + startId + " and " + (startId + onePageNum - 1));
                for (Entity entry : tableData) {
                    String fullPath = entry.getStr("fullpath");
                    String fileId = Utils.getFileId(fullPath);
                    if (fileId.startsWith("PM") && NumberUtil.isInteger(fileId.substring(2))) {
                        int pmId = Integer.parseInt(fileId.substring(2));
                        if (pmIdAndNlmIdMap.containsKey(pmId)) {
                            try {
                                Db.use().insert(Entity.create(downFullPathTable).set("pmId", pmId).set("nlmid", pmIdAndNlmIdMap.get(pmId)).set("fullpath", fullPath));
                                pmIdAndNlmIdMap.remove(pmId);
                            } catch (SQLException e) {
                                e.printStackTrace();
                            }
                        }
                    }
                }
            }
        }
        pmIdAndNlmIdMap.clear();
    }

    public static void mapSortValueIntLen2(Map<String, Map<String, Integer>> fuDanDeptInfoSumMap, String type, String stemFuDanDeptSumTable) {
        for (Map.Entry<String, Map<String, Integer>> entry : fuDanDeptInfoSumMap.entrySet()) {
            Map<String, Integer> stringIntegerMap = mapSortValueIntLen(entry.getValue(), 15);
            for (Map.Entry<String, Integer> map : stringIntegerMap.entrySet()) {
                try {
                    Db.use().insert(Entity.create(stemFuDanDeptSumTable).set("stem_fudan", type).set("dept_name", entry.getKey()).set("dept", map.getKey()).set("sum", map.getValue()));
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    public static Map<String, Integer> mapSortValueIntLen(Map<String, Integer> mapName, Integer len) {
        Map<String, Integer> result = new LinkedHashMap<>();
        mapName.entrySet().stream()
                .sorted(Map.Entry.<String, Integer>comparingByValue()
                        .reversed()).limit(len).forEachOrdered(e -> result.put(e.getKey(), e.getValue()));
        return result;
    }

    @Data
    public static class ZkyCategory {
        private String fq = "";
        private String type = "";
        private String top = "";
        private String name = "";
    }


}
